package com.ja10.books;

import java.io.IOException;
import java.security.interfaces.RSAKey;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;

import com.example.books.R;

import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.content.Intent;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.provider.ContactsContract.Data;
import android.util.Log;
import android.view.View;
import android.widget.EditText;
import android.widget.ImageView;
import android.widget.TextView;
import android.widget.Toast;

public class ReserveEntry extends Activity{
	
	ArrayList<BookList> data = null;
	String time = null;
	private Context context = null;
	private SimpleDatabaseHelper helper = null;
	private BookList book = null;
	private ReserveList reserve = null;
	Intent i = null;
	
	Calendar calendar = Calendar.getInstance();
	
	ReserveDay reserve_day = new ReserveDay(); 
	
	final String PATH = "http://192.168.3.190:8080/Books/book_img/";
	String table 		= null;
	int stock_books 	= 0;
	int rental_books 	= 0;
	int reserve_members = 0;
	int has_books		= stock_books - rental_books;
	
	// 各ウィジェットの取得
		ImageView img = null;
		TextView title = null;
		TextView writer = null;
		TextView publisher = null;
		TextView isbn = null;
		TextView publicationDate = null;
		TextView content = null;
		TextView text_stock_books = null;
		TextView text_rental_books = null;
		TextView text_reserve_books = null;
					
	Connection con = null;
	Statement smt = null;	

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		// TODO 自動生成されたメソッド・スタブ
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_reserve);
		
		// インテントを取得
		Intent intent = getIntent();
		// インテントに保存されたデータを取得
		String id = intent.getStringExtra("id");
		
		img = (ImageView)findViewById(R.id.img);
		title = (TextView)findViewById(R.id.txt_title);
		writer = (TextView)findViewById(R.id.writer);
		 publisher = (TextView)findViewById(R.id.txt_publisher);
		 isbn = (TextView)findViewById(R.id.txt_isbn);
		 publicationDate = (TextView)findViewById(R.id.txt_publicationDate);
		 content = (TextView)findViewById(R.id.txt_content);
		 text_stock_books = (TextView)findViewById(R.id.text_stock_books);
		 text_rental_books = (TextView)findViewById(R.id.text_rental_books);
		 text_reserve_books = (TextView)findViewById(R.id.text_reserve_books);
		 
			
		
		//　SQL文を記述
		 	String sql = "SELECT * FROM books WHERE id ='" + id + "'";
//		String sql = "SELECT * FROM books WHERE id ='2291'";
						
				try{
							
				//　データベースを接続するためのConnectionオブジェクトを取得
				//　SQLを発行するためのStatementオブジェクトを取得
						con = DBManager.getConnection();
						smt = con.createStatement();

				//　StatementオブジェクトのexecuteQuery()メソッドでSQLを発行(検索)
						ResultSet rs = smt.executeQuery( sql );					
						
						if(rs.first()) {
							// 書籍情報をBookListに格納
							book = new BookList();
							book.setId(rs.getInt("id"));
							book.setGenreName(rs.getInt("genre_id"));
							book.setPublisherName(rs.getInt("publisher_id"));
							book.setTitle(rs.getString("title"));
							book.setWriter(rs.getString("writer"));
							book.setContent(rs.getString("content"));
							book.setPublicationDate(rs.getString("publication_date"));
							book.setPictureBmp(rs.getString("picture"), 100);
							book.setIsbn(rs.getString("isbn"));
						} 
																	
						
						
						if(book != null) {
							// 各ウィジェットにデータをセット
							title.setText(book.getTitle());
							writer.setText(book.getWriter());
							publisher.setText(book.getPublisherName());
							isbn.setText(book.getIsbn());
							if(book.getPublicationDate() != null) {
								publicationDate.setText(book.getPublicationDate().replaceAll("-", "/"));
							}
							if(book.getContent() != null || book.getContent().length() > 0) {
								content.setText(book.getContent());
							} else {
								content.setVisibility(View.GONE);
							}
							
							img.setImageBitmap(book.getPictureBmp());
							
								
								String sql_reserve = "select count(*) as cnt from reserves where book_isbn = ?";
								PreparedStatement sql_r = con.prepareStatement(sql_reserve);
								
									//予約数 ＝ 検索結果のレコードの書籍ＩＤで予約テーブルを検索し、該当するレコード件数を予約数
									sql_r.setString(1, book.getIsbn());
									ResultSet rs_rsv = sql_r.executeQuery();
									if (rs_rsv.next()) {

										reserve_members = rs_rsv.getInt("cnt");
									}
								rs.close();
							stock_books 	= reserve_day.Numbers("books",book.getIsbn());
	//						rental_books	= RentalNumbers("rentals",book.getIsbn());
							String sb =  Integer.toString(stock_books);
							text_stock_books.setText(sb);
							rental_books	= reserve_day.RentalNumbers(book.getIsbn());
							sb =  Integer.toString(rental_books);
							text_rental_books.setText(sb);
//							reserve_books	= ReserveNumbers("rserves",book.getIsbn());
							reserve_members = reserve_day.ReserveMembers(calendar.getTime(), book.getIsbn());
							sb =  Integer.toString(reserve_members);
							text_reserve_books.setText(sb);
							
						}
			} catch (java.sql.SQLException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			} finally{

				if( smt != null ){
					try {
						smt.close();
					} catch (java.sql.SQLException e) {
						e.printStackTrace();
					}
				}
				if( con != null ){
					try{
						con.close();
					} catch (java.sql.SQLException e) {
						e.printStackTrace();
					}
				}
			}
		}
	
	//----------------　[予約]ボタンクリック時に呼び出されるメソッド
		public void btnReserve_onClick(View view){
			
			time = String.format("%02d-%02d-%02d-%02d-%02d-%02d",
										calendar.get(Calendar.YEAR),
										calendar.get(Calendar.MONTH)+1,
										calendar.get(Calendar.DATE),
										calendar.get(Calendar.HOUR_OF_DAY),
										calendar.get(Calendar.MINUTE),
										calendar.get(Calendar.SECOND));
			
			
			try{
				
				helper = new SimpleDatabaseHelper(this);
				SQLiteDatabase db = helper.getWritableDatabase();
				
				// 書籍情報をContentValuesに格納
				ContentValues cv = new ContentValues();
				
				cv.put("genre_name", book.getGenreName());
				cv.put("publisher_name", book.getPublisherName());
				cv.put("title", book.getTitle());
				cv.put("writer", book.getWriter());
				cv.put("content", book.getContent());
				cv.put("reserve_date", time);
				
				if(reserve_day.Numbers("books",book.getIsbn()) == reserve_day.RentalNumbers(book.getIsbn()) || reserve_members >= has_books){
					//すべて貸出中の場合、または　貸出可能冊数より多い予約者がいる場合
				cv.put("reserve_plan_date", reserve_day.ReturnPlanDate(calendar.getTime(),book.getIsbn(),reserve_members,stock_books,false));
				}else{
					cv.put("reserve_plan_date", time);	
				}
				cv.put("publication_date", book.getPublicationDate());
				book.setPictureByte(book.getPictureBmp());	// byte型の画像を設定
				cv.put("picture", book.getPictureByte());
				cv.put("isbn", book.getIsbn());
				
				// ---------二重予約を調べる
				//　SQL命令を準備
				String[] cols = {"isbn"};
				String[] params = {book.getIsbn()};
				Cursor cs = db.query("reserves", cols, "isbn = ?", params, null, null, null, null);
				
				// 重複がある場合登録せず通知
				if(cs.moveToFirst()){
					Toast.makeText(this, "すでに予約済みです", Toast.LENGTH_SHORT).show();
				}
				// 重複がない場合登録を実行・通知
				else{	
					
					con = DBManager.getConnection();
					smt = con.createStatement();				

					//ユーザー情報を取得
					SharedPreferences settings = getSharedPreferences(LoginActivity.PREFERENCES_FILE_NAME, Activity.MODE_PRIVATE);
					String loginId	= settings.getString("id", "");
					
					String sql_member = "select id from members where user_id = '" + loginId +"'";
					ResultSet rs = smt.executeQuery( sql_member );	
					
					ReserveList member = new ReserveList();
					if(rs.first()) {
						// member情報をReserveListに格納						
						member.setUserID(rs.getInt("id"));					
					} 
					
					
					//reserveテーブル（ブラウザ）に登録
					String sql_reseve_insert =   "insert into  reserves(ID,RESERVE_DATE,BOOK_ISBN,MEMBER_ID,MAIL_FLAG) " +
							"value(null,?,?,?,0)";
					
					PreparedStatement ist = con.prepareStatement(sql_reseve_insert);
	
					ist.setString(1, time);
					ist.setString(2, book.getIsbn());
					ist.setInt(3, member.getUserID());
					
					ist.executeUpdate();
					
					//reserveテーブル（アプリ）に登録
					db.insertWithOnConflict("reserves", null, cv, SQLiteDatabase.CONFLICT_REPLACE);	
					db.close();
					
					Toast.makeText(this, "予約申請をしました。登録確認メールをお待ちください", Toast.LENGTH_SHORT).show();
				}
			} catch(Exception e){
				e.printStackTrace();
			}
			if( smt != null ){try {smt.close();} catch (java.sql.SQLException e) {e.printStackTrace();}}
			if( con != null ){try{con.close();} catch (java.sql.SQLException e) {e.printStackTrace();}}
			
			
				finish();
		}
				
//----------------　[メインメニュー]ボタンクリック時に呼び出されるメソッド
		public void btnMenu_onClick(View view){
						
			Intent i = new Intent(this, MainActivity.class);
			startActivity(i);
						
			// ReserveDetailActivityを終了
				finish();
				}
		
//--
					
}